﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace DB
{
    public class DbBooking : IDbBooking
    {
        private DbConnection con;
        private SqlCommand dbCmd;

        public DbBooking()
        {
            con = DbConnection.GetInstance();
            dbCmd = null;
        }
        // HER Kunne med fordel bruges transaktion, da man ikke vil have at en booking bliver oprettet hvis insertTickets ikke går godt!
        public int InsertBooking(Booking bookingObj)
        {
            int rc = -1;
            string insert = @"INSERT INTO Booking_xp VALUES(@bookingId, @phoneNo, @showId, @reservation)";
            Console.WriteLine(insert);

            IDbShow dbShow = new DbShow();
            int showId = dbShow.GetShowId(bookingObj.Show);
            Console.WriteLine("showId fundet: " + showId);
            
            int nextBookingId = GetNextBookingId();
            Console.WriteLine("nextBookingId fundet: " + nextBookingId);

            
            try
            {
                dbCmd = con.GetDbCommand(insert);
                dbCmd.Parameters.AddWithValue("@bookingId", nextBookingId);
                dbCmd.Parameters.AddWithValue("@phoneNo", bookingObj.Customer.PhoneNo);
                dbCmd.Parameters.AddWithValue("@showId", showId); // Finder et showobjekt og derefter showID
                dbCmd.Parameters.AddWithValue("@reservation", bookingObj.Reservation);
                rc = dbCmd.ExecuteNonQuery();

                //Insert Ticketsto db.
                IDbTicket dbTicket = new DbTicket();
                foreach (Ticket ticket in bookingObj.Tickets)
                {
                    dbTicket.InsertTicket(bookingObj, ticket.Seat, ticket.Price, nextBookingId);
                }
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("InsertReservation(...) - SQL Error");
                Console.WriteLine(sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("InsertReservation(...) - Exception Error");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return rc;
        }

        public int GetBookingId(Booking bookingObj)
        {
            // En ticket fra en specifik booking, er nok til at gøre, at man finder et unikt bookingId.
            Ticket firstTicketInBooking = bookingObj.Tickets[0];

            int bookingId = -1;
            string select = "SELECT Booking_xp.bookingId " +
                            "FROM Booking_xp, Ticket_xp " +
                            "WHERE ticketId = @ticketId AND Booking_xp.bookingId = Ticket_xp.bookingId";
            Console.WriteLine(select);

            try
            {
                IDbTicket dbTicket = new DbTicket();
                int firstTicketInBookingId = dbTicket.GetTicketId(firstTicketInBooking);

                IDbShow dbShow = new DbShow();
                int showId = dbShow.GetShowId(bookingObj.Show);

                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@ticketId", firstTicketInBookingId);

                bookingId = (int)dbCmd.ExecuteScalar();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("sql Error in DbBooking - GetBookingId");
                Console.WriteLine(sqlE.Message);
            }

            catch (Exception e)
            {
                Console.WriteLine("Error in DbBooking - GetBookingId");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return bookingId;
        }


        public int GetNextBookingId()
        {
            String query = "SELECT MAX(bookingId) FROM Booking_xp";
            int nextInt = 0;
            try
            {
                dbCmd = con.GetDbCommand(query);
                nextInt = (Int32)dbCmd.ExecuteScalar();
                nextInt++;
                con.CloseConnection();
                return nextInt;
            }

            catch (InvalidCastException e)
            {
                //Console.WriteLine(e.Message);
                con.CloseConnection();
                return 0;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                con.CloseConnection();
                return -1;
            }
        }

        public int DeleteBooking(Booking bookingObj)
        {
            IDbTicket dbTicket = new DbTicket();
            int bookingId = dbTicket.FindBookingIdFromTicket(bookingObj.Tickets[0]);

            int rc = -1;
            string deleteSql = "DELETE FROM Booking_xp WHERE bookingId = @bookingId";
            Console.WriteLine(deleteSql);

            try
            {
                dbCmd = con.GetDbCommand(deleteSql);
                dbCmd.Parameters.AddWithValue("@bookingId", bookingId);
                rc = dbCmd.ExecuteNonQuery();
            }
            catch (SqlException sqlE)
            {
                Console.WriteLine("DeleteBooking(...) - SQL Error");
                Console.WriteLine(sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("DeleteBooking(...) - Exception Error");
                Console.WriteLine(e.Message);
            }
            finally
            {
                con.CloseConnection();
            }

            return rc;
        }

        public List<Booking> GetAllBookings()
        {
            return null;
        }

        public List<Booking> GetAllReservations()
        {
            return null;
        }

        public List<Booking> FindBookings(string phoneNo, bool retriveAssociation)
        {
            List<int> bookingids = new List<int>();

            List<Booking> bookings = new List<Booking>();
            SqlDataReader dbReader = null;
            Booking bookingObj = null;

            string select = "SELECT bookingId, phoneNo, reservation, date, cName "
                          + "FROM Booking_xp, Show_xp "
                          + "WHERE phoneNo = @phoneNo AND Booking_xp.showId = Show_xp.showId "
                          + "ORDER BY date";

            Console.WriteLine(select);

            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@phoneNo", phoneNo);
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    bookingObj = BuildBooking(dbReader);
                    bookings.Add(bookingObj);
                    bookingids.Add((int)dbReader["bookingId"]);
                }

                dbReader.Close();

                if (retriveAssociation)
                {
                    int index = 0;
                    foreach (Booking bookingObj2 in bookings)
                    {
                        //Associering til show.
                        string cName = bookingObj2.Show.CinemaHall.Name;
                        DateTime dateTime = bookingObj2.Show.DateTimeStart;

                        IDbShow dbShow = new DbShow();
                        Show showObj = dbShow.FindShow(dateTime, cName, true);
                        bookingObj2.Show = showObj;

                        //Associering til customer.
                        IDbCustomer dbCustomer = new DbCustomer();
                        Customer custObj = dbCustomer.FindCustomer(bookingObj2.Customer.PhoneNo);
                        bookingObj2.Customer = custObj;

                        //Associering til tickets.
                        IDbTicket dbTicket = new DbTicket();
                        bookingObj2.Tickets = dbTicket.FindTickets(bookingids[index], true); // Behøver ikke lave en nyt objekt da det er en List den sættes lig med.
                        index++;
                    }
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbBooking");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbBooking");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return bookings;
        }

        private Booking BuildBooking(SqlDataReader dbReader)
        {
            Booking bookingObj = new Booking();
            Show showObj = new Show();
            Customer custObj = new Customer();
            CinemaHall cHallObj = new CinemaHall();
            List<Ticket> tickets = new List<Ticket>();


            try
            {
                custObj.PhoneNo = dbReader["phoneNo"].ToString();
                bookingObj.Customer = custObj;
                bookingObj.Reservation = (int)dbReader["reservation"];
                showObj.DateTimeStart =(DateTime) dbReader["date"];
                cHallObj.Name = dbReader["cName"].ToString();
                showObj.CinemaHall = cHallObj;
                bookingObj.Show = showObj;
                
                bookingObj.Tickets = tickets;

            }
            catch (Exception e)
            {
                Console.WriteLine("Error in building bookingObj");
                Console.WriteLine(e.Message);
            }

            return bookingObj;
        }

    }
}
